<%-- 
    Document   : highscores
    Created on : Mar 27, 2011, 8:24:24 PM
    Author     : aurix
--%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>

<%-- HTML 5 --%>
<!DOCTYPE HTML>

<%@ page language="java" import="java.sql.*" %>

<%
    int TOPSCORE = 3;

    String WINS =  "SELECT winner AS player, COUNT(*) AS w "
            + "FROM GameSession "
            + "WHERE winner IS NOT NULL "
            + "GROUP BY winner";

    String P1 = "SELECT uid1 as player, COUNT(*) AS g "
            + "FROM GameSession "
            + "GROUP BY player";

    String P2 = "SELECT uid2 as player, COUNT(*) AS g "
            + "FROM GameSession "
            + "GROUP BY player";

    String SESSIONS = P1 + " UNION ALL " + P2;

    String GAMES = "SELECT player, COUNT(*) as g FROM (" + SESSIONS +") as games GROUP BY player";

    String RESULT = "SELECT player,w,g "
            + "FROM (("+WINS+") as wins NATURAL JOIN ("+GAMES+") as gms) "
            + "LIMIT 3;";
    /*
    //old sql statements in a more logical order.
    String WIN_COUNT = "CREATE TEMPORARY TABLE wins "
            + "AS ("
            + "SELECT winner AS player, COUNT(*) AS Wins "
            + "FROM GameSession "
            + "WHERE winner IS NOT NULL "
            + "GROUP BY winner);";

    String PARTICIPANTS = "CREATE TEMPORARY TABLE participants AS "
            + "(;";

    String GAMES_PLAYED = "CREATE TEMPORARY TABLE GameSessionsPlayed AS "
            + "(SELECT player, COUNT(*) as GameSessions "
            + "FROM participants GROUP BY player);";

    String SCORES = "CREATE TEMPORARY TABLE Scores AS "
            + "(SELECT player, Wins, GameSessions "
            + "FROM wins NATRUAL JOIN GameSessionsPlayed "
            + "GROUP BY player "
            + "ORDER BY Wins DESC);";

    String RESULT = "SELECT * FROM Scores LIMIT 3;";

 *
 */

    ResultSet r = null;

    try{
    //this part will need to be set to the correct name of the database
    //when merged into a single application.
    String url = "jdbc:mysql://localhost:3306/highscores";
    String username = "root";
    String password = "root";

     Class.forName("com.mysql.jdbc.Driver");

    //we're using JDBC 4, no Class.forName required.
    Connection c = DriverManager.getConnection(url,username,password);
    
    Statement s = c.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);

    //execute the statements
   // s.execute(WINS);
   // s.execute(P1);
   // s.execute(P2);
    s.execute(RESULT);

    //get the result from the final statement
    r = s.getResultSet();

    //must start iterator using .next() apparently
    r.next();

    //if it doesn't create a result set, quit.
    //I wish there was ResultSet.EMPTY_SET or something
    if (r == null)
        throw new Exception("Emptyset.");
 %>

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>High Scores - Crow's Nest</title>
    </head>
    <body>
        <h2>Top Scores</h2>
        <table id="topScores" border="1">
            <tr>
                <td>Pos.</td>
                <td>Name</td> <%--1--%>
                <td>W</td>  <%--2--%>
                <td>L</td>  <%--3--%>
                <td>W%</td>  <%-- calculated by java --%>
            </tr>
            <tr>
                <td>1</td>
                <td><%= r.getString(1).substring(0, 16) %></td>
                <td><%= r.getInt(2) %></td>
                <td><%= r.getInt(3) - r.getInt(2) %></td>
                <td><%= ((float)r.getInt(2)/(float)(r.getInt(3))) %></td>
            </tr>
            <%-- Next row --%>
            <%= r.next() %>
            <tr>
                <td>2</td>
                <td><%= r.getString(1).substring(0, 16) %></td>
                <td><%= r.getInt(2) %></td>
                <td><%= r.getInt(3) - r.getInt(2) %></td>
                <td><%= ((float)r.getInt(2)/(float)(r.getInt(3))) %></td>
            </tr>
             <%-- Next row --%>
            <%= r.next() %>
            <tr>
                <td>3</td>
                <td><%= r.getString(1).substring(0, 16) %></td>
                <td><%= r.getInt(2) %></td>
                <td><%= r.getInt(3) - r.getInt(2) %></td>
                <td><%= ((float)r.getInt(2)/(float)(r.getInt(3))) %></td>
            </tr>
        </table>
    </body>
</html>

<%
 } catch (Exception e){
    %>


    <html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>High Scores - Crow's Nest</title>
    </head>
    <body>
        <h2>Top Scores</h2>
        Scores unavailable.


         <%--comment out below this helps in debug--%>

        <%= e.getMessage() %>
        <%= e.getCause() %>

        <%
            StringBuilder b = new StringBuilder();
            for (StackTraceElement s : e.getStackTrace()){
                b.append(s.toString());
                b.append('\n');
                }
            String stacktrace = b.toString();
        %>
        <%= stacktrace %>
    </body>
    </html>

<%
 }
%>